! mkdir -p resources/benmapExtract data from BenMAP
This notebook describes how to extract data from BenMAP’s internal database and save the data for this project.
Create resources/benmap folder to store unzipped files.
Download BenMAP using wget. Install wget using homebrew if you don’t have one.
! brew install wgetWarning: wget 1.21.4 is already installed and up-to-date.
To reinstall 1.21.4, run:
brew reinstall wget! wget -c -O resources/benmap/benmap.exe https://www3.epa.gov/ttn/ecas/benmap/BenMAP-CE-Installer_x64_1.5.8.23.exe--2023-05-30 17:04:49-- https://www3.epa.gov/ttn/ecas/benmap/BenMAP-CE-Installer_x64_1.5.8.23.exe
Resolving www3.epa.gov (www3.epa.gov)... 2620:117:506f:15::f022, 134.67.21.34
Connecting to www3.epa.gov (www3.epa.gov)|2620:117:506f:15::f022|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1134521832 (1.1G) [application/x-msdownload]
Saving to: ‘resources/benmap/benmap.exe’
resources/benmap/be 100%[===================>] 1.06G 5.64MB/s in 2m 8s
2023-05-30 17:06:58 (8.47 MB/s) - ‘resources/benmap/benmap.exe’ saved [1134521832/1134521832]
Uncompress the exe file using a 7-Zip, p7zip.
! brew install p7zipWarning: p7zip 17.05 is already installed and up-to-date.
To reinstall 17.05, run:
brew reinstall p7zip! 7z x -y -oresources/benmap resources/benmap/benmap.exe
7-Zip [64] 17.04 : Copyright (c) 1999-2021 Igor Pavlov : 2017-08-28
p7zip Version 17.04 (locale=utf8,Utf16=on,HugeFiles=on,64 bits,8 CPUs LE)
Scanning the drive for archives:
0M Sca 1 file, 1134521832 bytes (1082 MiB)
Extracting archive: resources/benmap/benmap.exe
0% Ope --
Path = resources/benmap/benmap.exe
Type = PE
Physical Size = 1134521832
CPU = x86
Characteristics = Executable 32-bit
Created = 2017-04-05 15:33:08
Headers Size = 1024
Checksum = 1134551350
Name = 7zS.sfx.exe
Image Size = 503808
Section Alignment = 4096
File Alignment = 512
Code Size = 382976
Initialized Data Size = 106496
Uninitialized Data Size = 0
Linker Version = 14.10
OS Version = 6.0
Image Version = 0.0
Subsystem Version = 6.0
Subsystem = Windows GUI
DLL Characteristics = Relocated NX-Compatible TerminalServerAware
Stack Reserve = 1048576
Stack Commit = 4096
Heap Reserve = 1048576
Heap Commit = 4096
Image Base = 4194304
Comment = FileVersion: 16.4.0.0
FileVersion: 16.04
ProductVersion: 16.4.0.0
ProductVersion: 16.04
CompanyName: Igor Pavlov
FileDescription: 7z Setup SFX
InternalName: 7zS.sfx
LegalCopyright: Copyright (c) 1999-2016 Igor Pavlov
OriginalFilename: 7zS.sfx.exe
ProductName: 7-Zip
----
Path = [0]
Size = 1134035752
Packed Size = 1134035752
Virtual Size = 1134035752
Offset = 477184
--
Path = [0]
Type = 7z
Offset = 147
Physical Size = 1134035598
Tail Size = 7
Headers Size = 296
Method = LZMA2:24 BCJ
Solid = +
Blocks = 2
1% - Setup.m 3% - Setup.m 4% - Setup.m 5% - Setup.m 17% 1 - BENMAP50_DB.e 31% 1 - BENMAP50_DB.e 44% 1 - BENMAP50_DB.e 57% 1 - BENMAP50_DB.e 69% 2 - BenMAP50_GBD.ex 83% 2 - BenMAP50_GBD.ex 96% 2 - BenMAP50_GBD.ex Everything is Ok
Files: 5
Size: 1143498323
Compressed: 1134521832Unzipping the exe file produces several smaller exe files.
!ls resources/benmap/BENMAP50.FDB BenMAP50_GBD.exe Setup.msi benmap.exe.old
BENMAP50_DB.exe POPSIMDB.exe benmap.exe setup.exeNow unzip BENMAP50_DB.exe, which has the BenMAP internal database file.
!7z x -y -oresources/benmap resources/benmap/BENMAP50_DB.exe
7-Zip [64] 17.04 : Copyright (c) 1999-2021 Igor Pavlov : 2017-08-28
p7zip Version 17.04 (locale=utf8,Utf16=on,HugeFiles=on,64 bits,8 CPUs LE)
Scanning the drive for archives:
0M Sca 1 file, 687581809 bytes (656 MiB)
Extracting archive: resources/benmap/BENMAP50_DB.exe
0% Ope --
Path = resources/benmap/BENMAP50_DB.exe
Type = 7z
Offset = 215040
Physical Size = 687366769
Headers Size = 130
Method = LZMA2:24
Solid = -
Blocks = 1
0% - BENMAP50.FD 1% - BENMAP50.FD 2% - BENMAP50.FD 3% - BENMAP50.FD 4% - BENMAP50.FD 5% - BENMAP50.FD 6% - BENMAP50.FD 7% - BENMAP50.FD 8% - BENMAP50.FD 9% - BENMAP50.FD 10% - BENMAP50.FD 11% - BENMAP50.FD 12% - BENMAP50.FD 13% - BENMAP50.FD 14% - BENMAP50.FD 15% - BENMAP50.FD 16% - BENMAP50.FD 17% - BENMAP50.FD 18% - BENMAP50.FD 19% - BENMAP50.FD 20% - BENMAP50.FD 21% - BENMAP50.FD 22% - BENMAP50.FD 23% - BENMAP50.FD 24% - BENMAP50.FD 25% - BENMAP50.FD 26% - BENMAP50.FD 27% - BENMAP50.FD 28% - BENMAP50.FD 29% - BENMAP50.FD 30% - BENMAP50.FD 31% - BENMAP50.FD 32% - BENMAP50.FD 33% - BENMAP50.FD 34% - BENMAP50.FD 35% - BENMAP50.FD 36% - BENMAP50.FD 37% - BENMAP50.FD 38% - BENMAP50.FD 39% - BENMAP50.FD 40% - BENMAP50.FD 41% - BENMAP50.FD 42% - BENMAP50.FD 43% - BENMAP50.FD 44% - BENMAP50.FD 45% - BENMAP50.FD 46% - BENMAP50.FD 47% - BENMAP50.FD 48% - BENMAP50.FD 49% - BENMAP50.FD 50% - BENMAP50.FD 51% - BENMAP50.FD 52% - BENMAP50.FD 53% - BENMAP50.FD 54% - BENMAP50.FD 55% - BENMAP50.FD 56% - BENMAP50.FD 57% - BENMAP50.FD 58% - BENMAP50.FD 59% - BENMAP50.FD 60% - BENMAP50.FD 61% - BENMAP50.FD 62% - BENMAP50.FD 63% - BENMAP50.FD 64% - BENMAP50.FD 65% - BENMAP50.FD 66% - BENMAP50.FD 67% - BENMAP50.FD 68% - BENMAP50.FD 69% - BENMAP50.FD 70% - BENMAP50.FD 71% - BENMAP50.FD 72% - BENMAP50.FD 73% - BENMAP50.FD 74% - BENMAP50.FD 75% - BENMAP50.FD 76% - BENMAP50.FD 77% - BENMAP50.FD 78% - BENMAP50.FD 79% - BENMAP50.FD 80% - BENMAP50.FD 81% - BENMAP50.FD 82% - BENMAP50.FD 83% - BENMAP50.FD 84% - BENMAP50.FD 85% - BENMAP50.FD 86% - BENMAP50.FD 87% - BENMAP50.FD 88% - BENMAP50.FD 89% - BENMAP50.FD 90% - BENMAP50.FD 91% - BENMAP50.FD 92% - BENMAP50.FD 93% - BENMAP50.FD 94% - BENMAP50.FD 95% - BENMAP50.FD 96% - BENMAP50.FD 97% - BENMAP50.FD 98% - BENMAP50.FD 99% - BENMAP50.FD 100% Everything is Ok
Size: 5579063296
Compressed: 687581809!ls resources/benmap/BENMAP50.FDB BenMAP50_GBD.exe Setup.msi benmap.exe.old
BENMAP50_DB.exe POPSIMDB.exe benmap.exe setup.exeBENMAP50.FDB is BenMAP’s main database file. It is a database file used by Firebird, an open-source SQL relational database management system. The database is in the Firebird 2.5 format. So, put BENMAP50.FDB in your Firebird 2.5 server.
Connecting to the database
Let’s use ipython-sql to connect to the db.
! pip install ipython-sql fdb# for connecting to firebirdThe sql extension is already loaded. To reload it, use:
%reload_ext sqlShow the list of tables
SELECT a.RDB$RELATION_NAME
FROM RDB$RELATIONS a
WHERE COALESCE(RDB$SYSTEM_FLAG, 0) = 0 AND RDB$RELATION_TYPE = 0 * firebird+fdb://sysdba:***@localhost/benmap
0 rows affected.| rdb$relation_name | |
|---|---|
| 0 | QALYENTRIES |
| 1 | AGERANGES |
| 2 | BASELINEFUNCTIONALFORMS |
| 3 | COMMONFNFORMS |
| 4 | CRFUNCTIONCUSTOMENTRIES |
| ... | ... |
| 58 | METADATAINFORMATION |
| 59 | DATASETTYPES |
| 60 | DISTRIBUTIONTYPES |
| 61 | GEOGRAPHICAREAS |
| 62 | GEOGRAPHICAREAENTRIES |
63 rows × 1 columns